About calculation in workbooks

When are existing formulas recalculated?    Excel recalculates those cells that are dependent on other cells that contain changed values. This type of calculation helps to avoid unnecessary calculations. Excel also calculates workbooks each time they are opened.

Background calculation    As calculation proceeds, you can choose commands or perform actions such as entering numbers or formulas. Excel temporarily interrupts calculation to carry out the other commands or actions and then resumes calculation. The calculation process may take more time if the workbook contains a large number of formulas, or if the worksheets contain data tables or functions that automatically recalculate every time the workbook is recalculated. Also, the calculation process may take more time if the worksheets contain links to other worksheets or workbooks. You can control when calculation occurs by changing the calculation process to manual calculation.

15 digit precision Excel stores and calculates with 15 significant digits of precision.

Excel calculates stored, not displayed, values    The displayed, and printed, value depends on how you choose to format and display the stored value. For example, a cell that displays a date as "6/22/2008" also contains a serial number that is the stored value for the date in the cell. You can change the display of the date to another format (for example, to"22-Jun-2008"), but changing the display of a value on a worksheet does not change the stored value.

Change the precision of calculations    When a formula performs calculations, Excel usually uses the values stored in cells referenced by the formula. For example, if two cells each contain the value 10.005 and the cells are formatted to display values in currency format, the value $10.01 is displayed in each cell. If you add the two cells together, the result is $20.01 because Excel adds the stored values 10.005 and 10.005, not the displayed values. You can change the precision of calculations so that Excel uses the displayed value instead of the stored value when it recalculates formulas.

Change the number of times Excel calculates, or iterates, a formula    Excel cannot automatically calculate a formula that refers to the cell— either directly or indirectly— that contains the formula. This is called a circular reference. If a formula refers back to one of its own cells, you must determine how many times the formula should recalculate.

Note  You can use Solver when you need to find the optimum value for a particular cell by adjusting the values of several cells or when you want to apply specific limitations to one or more of the values in the calculation.

To be sure that older workbooks are calculated correctly, Excel behaves differently when you first open an old workbook than when you open a workbook created in the current version.

When you open a workbook created in the current version, Excel recalculates only the formulas that depend on cells that have changed.

When you use open a workbook that was created in a earlier version of Excel, all the formulas in the workbook— those that depend on cells that have changed and those that do not— are recalculated. This ensures that the workbook is fully optimized for the current Excel version.

Because complete recalculation can take longer than partial recalculation, opening a workbook that was not previously saved in the current Excel version can take longer than usual. After you save the workbook in the current version, it will open faster.